use master
go
drop database awe;
go
create database awe;
go
use awe;
go

create table accounts
(
id bigint identity primary key,
oid varchar(100) unique not null,
email varchar(100),
aemail varchar(100),
displayName nvarchar(30),
isAweOid bit not null,
creationDate DateTime not null,
mailoncomment bit not null,
mailOnAnswer bit not null
)

create table aweoids
(
id bigint identity primary key,
email varchar(100) unique not null,
"password" varchar(100) not null,
code bigint 
) 

create table recovers
(
id bigint identity primary key,
code bigint,
email varchar(100),
time datetime
)

create table posts
(
id bigint identity primary key,
title nvarchar(150),
body nvarchar(max) not null,
creationDate datetime not null,
parentId bigint,
ownerId bigint not null,
acceptedAnswerId bigint
)

create table comments
(
id bigint identity primary key,
postId bigint not null references posts(id) ON DELETE CASCADE,
ownerId bigint not null references accounts(id),
body nvarchar(600) not null,
creationDate datetime not null
)

create table notifications
(
id BIGINT IDENTITY PRIMARY KEY,
accountId BIGINT NOT NULL REFERENCES dbo.accounts(Id),
date DATETIME NOT NULL,
postId BIGINT NOT NULL references posts(id) on delete cascade,
causeId bigint,
ntype int NOT null,
isRead BIT NOT null,
sendEmail bit not null,
email varchar(100)
)

create table products
(
id bigint identity primary key,
code varchar(10) not null unique,
name varchar(100),
title varchar(250),
latestProductVersionId bigint
)

create table productVersions
(
id bigint identity primary key,
productId bigint not null references products(id),
"description" varchar(max) not null,
isActive bit not null,
"filename" varchar(100) not null,
dateCreated datetime not null,
versionNumber varchar(10) not null
)


alter table products add foreign key (latestProductVersionId) references productVersions(id)

create table purchases
(
id bigint identity primary key,
productVersionId bigint not null references productVersions(id),
paypalTransactionId varchar(100) not null unique,
quantity int not null,
amountPayed money,
userId bigint not null references accounts(id),
custom varchar(100) not null,
dateCreated DateTime not null,
isProcessed bit not null
)

create table licenses
(
id bigint identity primary key,
productVersionId bigint not null references productVersions(id),
userId bigint not null references accounts(id),
purchaseId bigint not null references purchases(id),
code varchar(100) not null,
"key" varchar(100) not null,
dateOfIssue datetime not null
)

create table upgrades
(
id bigint identity primary key,
fromVersionId bigint not null references productVersions(id),
toVersionId bigint not null references productVersions(id),
amountPayed money
)


insert aweoids(email,password,code) values('vnexster@gmail.com','n+PFdn7iBSAR2RW6DS9z61j2n9DzIF03aEpZXayP',0)
insert accounts(oid,email,displayName,isAweOid,creationDate,mailoncomment,mailonanswer)
values('1','vnexster@gmail.com','o',1,getdate(),0,0)


insert products(code,name,title) values('AWF', 'ASP.NET Project Awesome', 'ASP.NET Project Awesome - jQuery Ajax Controls')


insert productVersions(productId,"description",isActive,"filename",dateCreated, versionNumber) 
values(1,'asp.net jquery ajax controls',1,'awf.zip',GETDATE(),'1.0.0.0')



select * from purchases
select * from products
select * from productVersions
select * from licenses
select * from accounts
select * from aweoids